﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Transactions;

using AKMII.DMRA.Common;

namespace AKMII.DMRA.DataAccess
{
    public class ZipAccessor
    {
        /// <summary>
        /// Assign zips to agent.
        /// </summary>
        /// <param name="zipIds">Zip ids need assigned.</param>
        /// <param name="userId">assignment name.</param>
        /// <returns></returns>
        public bool UpdateZipAssigned(string assignmentName, string zipIds, int month, int year, bool isCurrentMonth)
        {
            const string SpName = "sp_Update_Assignment";

            try
            {
                if (zipIds.Length > 0)
                {
                    object[] parameters = new object[]
                    {
                        assignmentName,
                        zipIds,
                        month,
                        year,
                        isCurrentMonth
                    };

                    SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                        SpName, parameters);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in update assignment by id", ex);
            }

            return true;
        }

        public List<LeadBlock> GetLeadBlock(string userId, int month, int year, int type)
        {
            List<LeadBlock> zips = new List<LeadBlock>();
            const string SpName = "sp_Get_ZipHistory";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[]
                {
                    userId,
                    month,
                    year,
                    type
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    LeadBlock zip = new LeadBlock();

                    zip.ZipId = reader.GetInt32(0);
                    zip.ZipName = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    zip.County = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    zip.UserId = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    zip.State = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    zip.City = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                    zip.Division = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                    zip.Market = !reader.IsDBNull(7) ? reader.GetString(7) : string.Empty;
                    //zip.Territory = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                    zip.ActiveLeadCount = reader.GetInt32(9);
                    zip.LapsedLeadCount = reader.GetInt32(10);
                    zip.BusinessLeadCount = reader.GetInt32(11);
                    zip.NeighborLeadCount = reader.GetInt32(12);

                    if (type == 0)
                    {
                        zip.IsOrderInOneALN = Convert.ToBoolean(reader.GetInt32(13));
                        zip.IsOrderInOneBus = Convert.ToBoolean(reader.GetInt32(14));
                        zip.IsOrderInTwoALN = Convert.ToBoolean(reader.GetInt32(15));
                        zip.IsOrderInTwoBus = Convert.ToBoolean(reader.GetInt32(16));
                        zip.IsOrderInThreeALN = Convert.ToBoolean(reader.GetInt32(17));
                        zip.IsOrderInThreeBus = Convert.ToBoolean(reader.GetInt32(18));
                        zip.IsOrderInFourALN = Convert.ToBoolean(reader.GetInt32(19));
                        zip.IsOrderInFourBus = Convert.ToBoolean(reader.GetInt32(20));
                        zip.IsOrderInFiveALN = Convert.ToBoolean(reader.GetInt32(21));
                        zip.IsOrderInFiveBus = Convert.ToBoolean(reader.GetInt32(22));
                        zip.IsOrderInSixALN = Convert.ToBoolean(reader.GetInt32(23));
                        zip.IsOrderInSixBus = Convert.ToBoolean(reader.GetInt32(24));
                        zip.LeadBlockCode = !reader.IsDBNull(25) ? reader.GetString(25) : string.Empty;
                        zip.Latitude = !reader.IsDBNull(26) ? reader.GetString(26) : string.Empty;
                        zip.Longitude = !reader.IsDBNull(27) ? reader.GetString(27) : string.Empty;
                        zip.SecondaryCity = !reader.IsDBNull(31) ? reader.GetString(31) : string.Empty;

                        int orderDate = !reader.IsDBNull(32) ? reader.GetInt32(32) : 0;
                        zip.LastOrderDate = orderDate == 0 ? string.Empty : orderDate.ToString().Substring(4, 2) + orderDate.ToString().Substring(0, 4);
                    }
                    else
                    {
                        zip.Type = (OrderType)reader.GetInt32(13);
                        zip.LeadBlockCode = !reader.IsDBNull(14) ? reader.GetString(14) : string.Empty;
                        zip.Latitude = !reader.IsDBNull(15) ? reader.GetString(15) : string.Empty;
                        zip.Longitude = !reader.IsDBNull(16) ? reader.GetString(16) : string.Empty;
                        zip.UserAssigned = !reader.IsDBNull(17) ? reader.GetString(17) : string.Empty;
                        zip.SecondaryCity = !reader.IsDBNull(18) ? reader.GetString(18) : string.Empty;
                        //zip.ActiveStopCount = reader.GetInt32(18);
                        //zip.LapsedStopCount = reader.GetInt32(19);
                        zip.IsOrderInOneALN = true;
                    }

                    zips.Add(zip);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get zips by id", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return zips;
        }

        public List<LeadBlock> GetLeadBlock(string userId, int month, int year, string county, string cities)
        {
            List<LeadBlock> lbs = new List<LeadBlock>();
            const string SpName = "sp_Get_ZipHistoryByCountyAndCity";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[]
                {
                    userId,
                    month,
                    year,
                    county,
                    cities
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    LeadBlock leadBlock = new LeadBlock();

                    leadBlock.ZipId = reader.GetInt32(0);
                    leadBlock.ZipName = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    leadBlock.County = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    leadBlock.UserId = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    leadBlock.State = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    leadBlock.City = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                    leadBlock.Division = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                    leadBlock.Market = !reader.IsDBNull(7) ? reader.GetString(7) : string.Empty;
                    //leadBlock.Territory = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                    leadBlock.ActiveLeadCount = reader.GetInt32(9);
                    leadBlock.LapsedLeadCount = reader.GetInt32(10);
                    leadBlock.BusinessLeadCount = reader.GetInt32(11);
                    leadBlock.NeighborLeadCount = reader.GetInt32(12);

                    leadBlock.IsOrderInOneALN = Convert.ToBoolean(reader.GetInt32(13));
                    leadBlock.IsOrderInOneBus = Convert.ToBoolean(reader.GetInt32(14));
                    leadBlock.IsOrderInTwoALN = Convert.ToBoolean(reader.GetInt32(15));
                    leadBlock.IsOrderInTwoBus = Convert.ToBoolean(reader.GetInt32(16));
                    leadBlock.IsOrderInThreeALN = Convert.ToBoolean(reader.GetInt32(17));
                    leadBlock.IsOrderInThreeBus = Convert.ToBoolean(reader.GetInt32(18));
                    leadBlock.IsOrderInFourALN = Convert.ToBoolean(reader.GetInt32(19));
                    leadBlock.IsOrderInFourBus = Convert.ToBoolean(reader.GetInt32(20));
                    leadBlock.IsOrderInFiveALN = Convert.ToBoolean(reader.GetInt32(21));
                    leadBlock.IsOrderInFiveBus = Convert.ToBoolean(reader.GetInt32(22));
                    leadBlock.IsOrderInSixALN = Convert.ToBoolean(reader.GetInt32(23));
                    leadBlock.IsOrderInSixBus = Convert.ToBoolean(reader.GetInt32(24));
                    leadBlock.LeadBlockCode = !reader.IsDBNull(25) ? reader.GetString(25) : string.Empty;
                    leadBlock.Latitude = !reader.IsDBNull(26) ? reader.GetString(26) : string.Empty;
                    leadBlock.Longitude = !reader.IsDBNull(27) ? reader.GetString(27) : string.Empty;
                    leadBlock.RealActiveLeadCount = reader.GetInt32(29);
                    leadBlock.RealLapsedLeadCount = reader.GetInt32(30);
                    leadBlock.SecondaryCity = !reader.IsDBNull(31) ? reader.GetString(31) : string.Empty;

                    int orderDate = !reader.IsDBNull(32) ? reader.GetInt32(32) : 0;
                    leadBlock.LastOrderDate = orderDate == 0 ? string.Empty : orderDate.ToString().Substring(4, 2) + orderDate.ToString().Substring(0, 4);
                    leadBlock.UserAssigned = !reader.IsDBNull(33) ? reader.GetString(33) : string.Empty;

                    string maxPcLeadType = !reader.IsDBNull(34) ? reader.GetString(34) : string.Empty;

                    if (leadBlock.LeadBlockCode.EndsWith("0000") ||
                        (leadBlock.LeadBlockCode.EndsWith("9999") && (maxPcLeadType == "S" || maxPcLeadType == "R")))
                    {
                        leadBlock.IsDLA = true;
                    }

                    lbs.Add(leadBlock);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get zips by id, terr and city", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return lbs;
        }

        public List<SimpleLeadBlock> GetSimpleLeadBlock(string userId, int month, int year, int type)
        {
            List<SimpleLeadBlock> zips = new List<SimpleLeadBlock>();
            const string SpName = "sp_Map_Get_Zips";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[]
                {
                    userId,
                    month,
                    year,
                    type
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    SimpleLeadBlock zip = new SimpleLeadBlock();

                    zip.ZipId = reader.GetInt32(0);
                    zip.ZipName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    zip.UserId = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    zip.City = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    //zip.Territory = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    zip.ActiveLeadCount = reader.GetInt32(5);
                    zip.LapsedLeadCount = reader.GetInt32(6);
                    zip.BusinessLeadCount = reader.GetInt32(7);
                    zip.NeighborLeadCount = reader.GetInt32(8);
                    zip.LeadBlockCode = !reader.IsDBNull(9) ? reader.GetString(9) : null;
                    zip.Latitude = !reader.IsDBNull(10) ? reader.GetString(10) : null;
                    zip.Longitude = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    zip.UserAssigned = !reader.IsDBNull(12) ? reader.GetString(12) : null;

                    if (type == 0)
                    {
                        zip.IsOrdered = Convert.ToBoolean(reader.GetInt32(13));
                        int orderDate = !reader.IsDBNull(14) ? reader.GetInt32(14) : 0;
                        zip.LastOrderDate = orderDate == 0 ? string.Empty : orderDate.ToString().Substring(4, 2) + orderDate.ToString().Substring(0, 4);
                    }
                    else
                    {
                        // Nothing to do
                    }

                    zips.Add(zip);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get simple zips by id", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return zips;
        }

        public List<SimpleLeadBlock> GetSimpleLeadBlock(string userId, int month, int year, int type, string county, string cities)
        {
            List<SimpleLeadBlock> lbs = new List<SimpleLeadBlock>();
            const string SpName = "sp_Map_GetZipsByCountyAndCity";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[]
                {
                    userId,
                    month,
                    year,
                    type,
                    county,
                    cities
                };


                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    SimpleLeadBlock leadBlock = new SimpleLeadBlock();
                    string maxPcLeadType = string.Empty;

                    leadBlock.ZipId = reader.GetInt32(0);
                    leadBlock.ZipName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    leadBlock.UserId = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    leadBlock.City = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    //leadBlock.Territory = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    leadBlock.County = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    leadBlock.ActiveLeadCount = reader.GetInt32(5);
                    leadBlock.LapsedLeadCount = reader.GetInt32(6);
                    leadBlock.BusinessLeadCount = reader.GetInt32(7);
                    leadBlock.NeighborLeadCount = reader.GetInt32(8);
                    leadBlock.LeadBlockCode = !reader.IsDBNull(9) ? reader.GetString(9) : null;
                    leadBlock.Latitude = !reader.IsDBNull(10) ? reader.GetString(10) : null;
                    leadBlock.Longitude = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    leadBlock.UserAssigned = !reader.IsDBNull(12) ? reader.GetString(12) : null;

                    if (type == 0)
                    {
                        leadBlock.IsOrdered = Convert.ToBoolean(reader.GetInt32(13));
                        int orderDate = !reader.IsDBNull(14) ? reader.GetInt32(14) : 0;
                        leadBlock.LastOrderDate = orderDate == 0 ? string.Empty : orderDate.ToString().Substring(4, 2) + orderDate.ToString().Substring(0, 4);
                        leadBlock.SecondaryCity = !reader.IsDBNull(15) ? reader.GetString(15) : string.Empty;

                        maxPcLeadType = !reader.IsDBNull(16) ? reader.GetString(16) : string.Empty;
                        leadBlock.RealActiveLeadCount = reader.GetInt32(17);
                        leadBlock.RealLapsedLeadCount = reader.GetInt32(18);
                    }
                    else
                    {
                        // Nothing to do
                        maxPcLeadType = !reader.IsDBNull(15) ? reader.GetString(15) : string.Empty;
                        leadBlock.RealActiveLeadCount = reader.GetInt32(16);
                        leadBlock.RealLapsedLeadCount = reader.GetInt32(17);
                    }

                    if (leadBlock.LeadBlockCode.EndsWith("0000") ||
                        (leadBlock.LeadBlockCode.EndsWith("9999") && (maxPcLeadType == "S" || maxPcLeadType == "R")))
                    {
                        leadBlock.IsDLA = true;
                    }

                    lbs.Add(leadBlock);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get simple zips by id, counties, cities", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return lbs;
        }

        public LeadBlock GetLeadBlcokTypesCount(string year, string month)
        {
            LeadBlock zips = new LeadBlock();
            const string SpName = "sp_Get_ZipsTypeCount";
            SqlDataReader rd = null;
            try
            {
                object[] sqlparametres = new object[]
                {
                    year,
                    month
                };
                rd = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, sqlparametres);
                while (rd.Read())
                {
                    zips.ActiveLeadCount = int.Parse(rd[1].ToString());
                    zips.LapsedLeadCount = int.Parse(rd[2].ToString());
                    zips.BusinessLeadCount = int.Parse(rd[3].ToString());
                    zips.NeighborLeadCount = int.Parse(rd[4].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get zips types count ", ex);
            }
            finally
            {
                if (rd == null)
                {
                    rd.Close();
                }
            }
            return zips;
        }

        public LeadBlock GetLeadBlcokTypesCountForTest(string year, string month)
        {
            LeadBlock zips = new LeadBlock();
            const string SpName = "sp_Get_ZipsTypeCountForTest";
            SqlDataReader rd = null;
            try
            {
                object[] sqlparametres = new object[]
                {
                    year,
                    month
                };
                rd = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, sqlparametres);
                while (rd.Read())
                {
                    zips.ActiveLeadCount = int.Parse(rd[1].ToString());
                    zips.LapsedLeadCount = int.Parse(rd[2].ToString());
                    zips.BusinessLeadCount = int.Parse(rd[3].ToString());
                    zips.NeighborLeadCount = int.Parse(rd[4].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get zips types count for test", ex);
            }
            finally
            {
                if (rd == null)
                {
                    rd.Close();
                }
            }
            return zips;
        }

        public List<LeadBlock> GetLeadBlock()
        {
            List<LeadBlock> leadBlocks = new List<LeadBlock>();
            const string SpName = "sp_Get_Zips";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    LeadBlock leadBlock = new LeadBlock();

                    leadBlock.ZipId = reader.GetInt32(0);
                    leadBlock.ZipName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    leadBlock.Zip4Name = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    leadBlock.UserId = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    leadBlock.State = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    leadBlock.City = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    leadBlock.Division = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    leadBlock.Market = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    //leadBlock.Territory = !reader.IsDBNull(8) ? reader.GetString(8) : null;
                    leadBlock.ActiveLeadCount = reader.GetInt32(9);
                    leadBlock.LapsedLeadCount = reader.GetInt32(10);
                    leadBlock.BusinessLeadCount = reader.GetInt32(11);
                    leadBlock.NeighborLeadCount = reader.GetInt32(12);

                    leadBlocks.Add(leadBlock);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get lead blcoks", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return leadBlocks;
        }

        public List<LeadBlock> GetLeadBlock(string zipName)
        {
            List<LeadBlock> leadBlocks = new List<LeadBlock>();
            const string SpName = "sp_Get_LeadBlockId";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { zipName };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    LeadBlock leadBlock = new LeadBlock();

                    leadBlock.ZipId = reader.GetInt32(0);

                    leadBlocks.Add(leadBlock);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get lead block id by zip code", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return leadBlocks;
        }

        public List<ZipCode> GetZipCodes(string mdNumber, int month, int year)
        {
            List<ZipCode> zipCodes = new List<ZipCode>();
            const string SpName = "sp_Get_ZipCode";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { mdNumber, month, year };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    ZipCode zipCode = new ZipCode();

                    zipCode.ZipName = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    zipCode.Longitude = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    zipCode.Latitude = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    zipCode.ActiveLeadCount = reader.GetInt32(3);
                    zipCode.LapsedLeadCount = reader.GetInt32(4);
                    zipCode.BusinessLeadCount = reader.GetInt32(5);
                    zipCode.NeighborLeadCount = reader.GetInt32(6);
                    zipCode.Territory = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    zipCode.IsOrdered = Convert.ToBoolean(reader.GetInt32(8));
                    zipCode.UserId = !reader.IsDBNull(9) ? reader.GetString(9) : null;

                    zipCodes.Add(zipCode);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in getting zip code", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return zipCodes;
        }

        public bool InsertLeadBlock()
        {
            const string SpName = "sp_Insert_Zips";

            try
            {
                object[] parameters = new object[] { };

                SqlHelper.ExecuteNonQueryNoTimeout(Constants.ConnectionString,
                    SpName, parameters);

            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in insert zips", ex);
            }

            return true;
        }

        public List<LeadBlock> GetLeadBlock(string userId, int month, int year)
        {
            List<LeadBlock> codes = new List<LeadBlock>();
            const string SpName = "sp_Get_RequesterCode";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { userId, month.ToString(), year.ToString() };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    LeadBlock code = new LeadBlock();

                    code.LeadBlockCode = !reader.IsDBNull(0) ? reader.GetString(0) : string.Empty;
                    //code.Territory = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    code.Market = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    code.ActiveLeadCount = reader.GetInt32(3);
                    code.LapsedLeadCount = reader.GetInt32(4);
                    code.BusinessLeadCount = reader.GetInt32(5);
                    code.NeighborLeadCount = reader.GetInt32(6);
                    code.Latitude = !reader.IsDBNull(7) ? reader.GetString(7) : string.Empty;
                    code.Longitude = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                    code.UserAssigned = !reader.IsDBNull(9) ? reader.GetString(9) : string.Empty;
                    code.City = !reader.IsDBNull(10) ? reader.GetString(10) : string.Empty;
                    code.UserId = !reader.IsDBNull(11) ? reader.GetString(11) : string.Empty;
                    code.Division = !reader.IsDBNull(12) ? reader.GetString(12) : string.Empty;
                    
                    codes.Add(code);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get zip requester code", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return codes;
        }

        public LeadBlock GetLeadBlockByCode(string leadBlockCode)
        {
            LeadBlock code = new LeadBlock();
            const string SpName = "sp_Get_LeadBlock";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { leadBlockCode };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                if (reader.Read())
                {
                    code = new LeadBlock();

                    code.LeadBlockCode = !reader.IsDBNull(0) ? reader.GetString(0) : string.Empty;
                    //code.Territory = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    code.Market = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    code.ActiveLeadCount = reader.GetInt32(3);
                    code.LapsedLeadCount = reader.GetInt32(4);
                    code.BusinessLeadCount = reader.GetInt32(5);
                    code.NeighborLeadCount = reader.GetInt32(6);
                    code.Latitude = !reader.IsDBNull(7) ? reader.GetString(7) : string.Empty;
                    code.Longitude = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                    code.UserAssigned = !reader.IsDBNull(9) ? reader.GetString(9) : string.Empty;
                    code.State = !reader.IsDBNull(10) ? reader.GetString(10) : string.Empty;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get lead block by lead block code", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return code;
        }

        public List<Assignment> GetAssignments(string userId, int month, int year)
        {
            List<Assignment> assignments = new List<Assignment>();

            const string SpName = "sp_Get_Assignments";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { userId, month, year };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Assignment assignment = new Assignment();

                    assignment.AgentName = !reader.IsDBNull(0) ? reader.GetString(0) : string.Empty;
                    assignment.ActiveLeadCount = reader.GetInt32(1);
                    assignment.LapsedLeadCount = reader.GetInt32(2);
                    assignment.BusinessLeadCount = reader.GetInt32(3);
                    assignment.NeighborLeadCount = reader.GetInt32(4);

                    assignments.Add(assignment);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get assignments", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return assignments;
        }
    }
}
